package com.example.dev_demo.excel.imports;

import org.apache.poi.ss.usermodel.*;
import org.hibernate.validator.HibernateValidator;
import org.springframework.util.CollectionUtils;

import javax.validation.Validation;
import javax.validation.Validator;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

public class ExcelImportUtil {

    private static final Validator validator = Validation.byProvider(HibernateValidator.class)
            .configure()
            .failFast(false)
            .buildValidatorFactory()
            .getValidator();

    /**
     * 解析Excel并进行数据校验
     */
    public static <T> ImportResult<T> parseAndValidateExcel(InputStream inputStream,
                                                            Class<T> clazz,
                                                            Map<String, String> headerFieldMap,int headerRowNum) throws IOException {
        List<T> dataList = new ArrayList<>();
        List<ImportError> errorList = new ArrayList<>();

        try (Workbook workbook = WorkbookFactory.create(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null) {
                errorList.add(new ImportError(headerRowNum,0, "", "Excel文件中没有工作表"));
                return new ImportResult<>(dataList, errorList);
            }

//            int headerRowNum = 1;
            Row headerRow = sheet.getRow(headerRowNum);
            if (headerRow == null) {
                errorList.add(new ImportError(headerRowNum, "", "Excel文件中没有表头行"));
                return new ImportResult<>(dataList, errorList);
            }

            // 验证表头
            validateHeaders(headerRow, headerFieldMap, errorList);

            // 解析数据行并校验
            int lastRowNum = sheet.getLastRowNum();
            for (int rowNum = headerRowNum + 1; rowNum <= lastRowNum; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null){ continue;}

                T entity = parseRowToEntity(row, headerRow, headerFieldMap, clazz, rowNum + 1, errorList);
                if (entity != null) {
                    dataList.add(entity);
                    validateEntity(entity, rowNum + 1, errorList, headerFieldMap,headerRowNum);
                }
            }
        }

        return new ImportResult<>(dataList, errorList);
    }

    /**
     * 将错误按行号分组
     */
    public static Map<Integer, List<ImportError>> groupErrorsByRow(List<ImportError> errors) {
        if (CollectionUtils.isEmpty(errors)) {
            return new HashMap<>();
        }
        return errors.stream()
                .collect(Collectors.groupingBy(ImportError::getRowNum));
    }

    /**
     * 根据表头名获取列索引
     */
    public static int getColumnIndexByHeader(Row headerRow, String headerName, Map<String, String> headerFieldMap) {
        // 如果传入的是字段名，先转换为表头名
        String targetHeader = headerName;
        if (!headerFieldMap.containsKey(headerName)) {
            // 查找字段名对应的表头名
            for (Map.Entry<String, String> entry : headerFieldMap.entrySet()) {
                if (entry.getValue().equals(headerName)) {
                    targetHeader = entry.getKey();
                    break;
                }
            }
        }

        // 查找表头对应的列索引
        for (Cell cell : headerRow) {
            if (cell != null) {
                String header = getCellValue(cell).toString().trim();
                if (targetHeader.equals(header)) {
                    return cell.getColumnIndex();
                }
            }
        }
        return -1;
    }

    /**
     * 验证表头是否完整
     */
    private static void validateHeaders(Row headerRow, Map<String, String> headerFieldMap, List<ImportError> errorList) {
        Set<String> existingHeaders = new HashSet<>();

        // 收集现有表头
        for (Cell cell : headerRow) {
            if (cell != null) {
                String header = getCellValue(cell).toString().trim();
                existingHeaders.add(header);
            }
        }

        // 检查必要的表头是否存在
        for (String requiredHeader : headerFieldMap.keySet()) {
            if (!existingHeaders.contains(requiredHeader)) {
                errorList.add(new ImportError(headerRow.getRowNum(),headerRow.getRowNum(), requiredHeader, "缺少必要的表头"));
            }
        }
    }

    /**
     * 将行数据解析为实体对象
     */
    private static <T> T parseRowToEntity(Row row, Row headerRow, Map<String, String> headerFieldMap,
                                          Class<T> clazz, int rowNum, List<ImportError> errorList) {
        try {
            T entity = clazz.getDeclaredConstructor().newInstance();

            for (Cell cell : row) {
                int columnIndex = cell.getColumnIndex();
                String header = getHeaderByCell(columnIndex, headerRow);
                String fieldName = headerFieldMap.get(header);

                if (fieldName == null) {
                    continue; // 跳过未配置的字段
                }

                try {
                    setFieldValue(entity, fieldName, cell);
                } catch (Exception e) {
                    // 记录字段解析错误
                    errorList.add(new ImportError(rowNum, header,
                            "数据格式错误: " + e.getMessage()));
                }
            }

            return entity;
        } catch (Exception e) {
            errorList.add(new ImportError(rowNum, "",
                    "创建实体对象失败: " + e.getMessage()));
            return null;
        }
    }

    /**
     * 对实体对象进行校验
     */
    private static <T> void validateEntity(T entity, int rowNum, List<ImportError> errorList,
                                           Map<String, String> headerFieldMap,int headerRowNum) {
        Set<javax.validation.ConstraintViolation<T>> violations = validator.validate(entity);
        if (!violations.isEmpty()) {
            // 创建字段名到表头名的映射
            Map<String, String> fieldToHeaderMap = new HashMap<>();
            for (Map.Entry<String, String> entry : headerFieldMap.entrySet()) {
                fieldToHeaderMap.put(entry.getValue(), entry.getKey());
            }

            for (javax.validation.ConstraintViolation<T> violation : violations) {
                String fieldName = violation.getPropertyPath().toString();
                // 将字段名转换为表头名
                String headerName = fieldToHeaderMap.getOrDefault(fieldName, fieldName);
                errorList.add(new ImportError(headerRowNum,rowNum, headerName, violation.getMessage()));
            }
        }
    }

    /**
     * 根据单元格列索引获取对应的表头文本
     */
    private static String getHeaderByCell(int columnIndex, Row headerRow) {
        if (headerRow == null) {
            return "";
        }
        Cell headerCell = headerRow.getCell(columnIndex);
        if (headerCell == null) {
            return "";
        }

        Object value = getCellValue(headerCell);
        return value != null ? value.toString().trim() : "";
    }

    /**
     * 通过反射将单元格值设置到实体对象的指定字段
     */
    private static void setFieldValue(Object obj, String fieldName, Cell cell) throws Exception {
        if (obj == null || fieldName == null || cell == null) {
            return;
        }

        Field field = obj.getClass().getDeclaredField(fieldName);
        field.setAccessible(true);

        Object cellValue = getCellValue(cell);
        if (cellValue == null) {
            return;
        }

        Class<?> fieldType = field.getType();
        if (fieldType == String.class) {
            field.set(obj, cellValue.toString().trim());
        } else if (fieldType == Integer.class || fieldType == int.class) {
            if (cellValue instanceof Number) {
                field.set(obj, ((Number) cellValue).intValue());
            } else {
                field.set(obj, Integer.parseInt(cellValue.toString().trim()));
            }
        } else if (fieldType == Long.class || fieldType == long.class) {
            if (cellValue instanceof Number) {
                field.set(obj, ((Number) cellValue).longValue());
            } else {
                field.set(obj, Long.parseLong(cellValue.toString().trim()));
            }
        } else if (fieldType == Double.class || fieldType == double.class) {
            if (cellValue instanceof Number) {
                field.set(obj, ((Number) cellValue).doubleValue());
            } else {
                field.set(obj, Double.parseDouble(cellValue.toString().trim()));
            }
        } else if (fieldType == Date.class) {
            if (cellValue instanceof Date) {
                field.set(obj, cellValue);
            } else if (cellValue instanceof String) {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                field.set(obj, sdf.parse(cellValue.toString().trim()));
            }
        } else if (fieldType == Boolean.class || fieldType == boolean.class) {
            if (cellValue instanceof Boolean) {
                field.set(obj, cellValue);
            } else if (cellValue instanceof String) {
                field.set(obj, Boolean.parseBoolean(cellValue.toString().trim()));
            }
        }
    }

    /**
     * 获取单元格的值
     */
    private static Object getCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    double value = cell.getNumericCellValue();
                    if (value == (long) value) {
                        return (long) value;
                    } else {
                        return value;
                    }
                }
            case BOOLEAN:
                return cell.getBooleanCellValue();
            case FORMULA:
                // 对于公式单元格，获取计算结果
                Workbook workbook = cell.getSheet().getWorkbook();
                FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
                CellValue cellValue = evaluator.evaluate(cell);
                if (cellValue.getCellType() == CellType.NUMERIC) {
                    return cellValue.getNumberValue();
                } else if (cellValue.getCellType() == CellType.STRING) {
                    return cellValue.getStringValue();
                } else if (cellValue.getCellType() == CellType.BOOLEAN) {
                    return cellValue.getBooleanValue();
                }
                return cell.getCellFormula();
            default:
                return null;
        }
    }

    /**
     * 导入结果封装类
     */
    public static class ImportResult<T> {
        private List<T> dataList;
        private List<ImportError> errorList;

        public ImportResult(List<T> dataList, List<ImportError> errorList) {
            this.dataList = dataList;
            this.errorList = errorList;
        }

        public List<T> getDataList() {
            return dataList;
        }

        public List<ImportError> getErrorList() {
            return errorList;
        }

        public boolean hasErrors() {
            return !CollectionUtils.isEmpty(errorList);
        }
    }

    /**
     * 导入错误信息封装类
     */
    public static class ImportError {
        private int headerNum;
        private int rowNum;        // 行号（用户可见，从1开始）
        private String fieldName;  // 字段名（表头名）
        private String errorMsg;   // 错误信息

        public ImportError(int rowNum, String fieldName, String errorMsg) {
            this.rowNum = rowNum;
            this.fieldName = fieldName;
            this.errorMsg = errorMsg;
        }

        public ImportError(int headerNum, int rowNum, String fieldName, String errorMsg) {
            this.headerNum = headerNum;
            this.rowNum = rowNum;
            this.fieldName = fieldName;
            this.errorMsg = errorMsg;
        }

        public int getHeaderNum() {
            return headerNum;
        }

        public int getRowNum() {
            return rowNum;
        }

        public String getFieldName() {
            return fieldName;
        }

        public String getErrorMsg() {
            return errorMsg;
        }
    }
}
    